package co.com.colcomercio.interfaceRecaudos.serPublicos.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import co.com.colcomercio.conf.dao.impl.DAOSuper;
import co.com.colcomercio.interfaceRecaudos.serPublicos.dao.RecaudosServiciosPublicosDAO;
import co.com.colcomercio.interfaceRecaudos.serPublicos.valueObject.RecaudoServiciosPublicosVO;
import co.com.colcomercio.util.Formatos;
import co.com.colcomercio.util.ManejoParam;
import co.com.colcomercio.util.constants.GeneralConstants;
import co.com.colcomercio.util.exception.DAOException;

public class RecaudosServiciosPublicosDAOImpl extends DAOSuper implements RecaudosServiciosPublicosDAO{
	
	/**
	 * Metodo que retorna un listado
	 * de NITS para los cuales se hace 
	 * recaudo
	 * @return
	 */
	public List<String> getNITsRecaudo() throws DAOException
	{
		List<String> nits 		= null;
		Connection con 			= null;
		PreparedStatement pre 	= null;
		ResultSet rs 			= null;
		
			try
			{
				con = getConnection();
				pre = con.prepareStatement("select distinct b.value as nit from ticketitemproperties a inner join ticketitemproperties b on (a.pos = b.pos and a.localid = b.localid and a.ticketnumber = b.ticketnumber) where a.name = 'companyType' and a.value = 'PUB' and b.name = 'companyNit' ");
				rs  = pre.executeQuery();
				
				nits = new ArrayList<String>();
				
				while(rs.next())
				{
					nits.add(rs.getString(1));	
				}
			}
			catch(Exception e)
			{
				Logger.getLogger(RecaudosServiciosPublicosDAOImpl.class.getName()).log(Level.SEVERE, "Problema consultando Nits ", e);
				throw new DAOException("Problema consultando Nits "+e);
			}
			finally
			{
				closeResultSet(rs);
				closePreparedStatement(pre);
				closeConnection(con);
			}
		
		return nits;
	}

	/**
	 * Metodo que consulta el listado de recaudos hechos para
	 * un NIT en una fecha determinada que se recibe por
	 * parametro con formato yyyy-mm-dd
	 * @return
	 * @throws SQLException
	 */
	
	public List<RecaudoServiciosPublicosVO> getInfRecaudosNIT(String nit, String fecha) throws DAOException
	{
		List<RecaudoServiciosPublicosVO> recaudos 		= null;
		RecaudoServiciosPublicosVO recaudoServiciosPublicosVO = null;
		Connection con 			= null;
		PreparedStatement pre 	= null;
		ResultSet rs 			= null;
		String where 			= null;
		String fechaDesde 		= null;
		String fechaHasta 		= null;

		
			try
			{
				String sql = "select 	a.pos, a.localid, a.ticketnumber ,a.opendate, b.description, b.id as id_tiketitems, "
									+ "	c.value as companyType, c1.value as nit, c2.value as nfactura,c3.value as vfactura, d.opendate as payment_fecha, d.affectedamount as amount "
									+ " from		tickets a "
									+ " inner join 	ticketitems 			b 	on (b.pos 	= a.pos and b.localid 	= a.localid and b.ticketnumber 	= a.ticketnumber and b.opendate 	= a.opendate) "
									+ " inner join 	ticketitemproperties 	c 	on (c.pos 	= b.pos and c.localid 	= b.localid and c.ticketnumber 	= b.ticketnumber and c.opendate 	= b.opendate and c.id 	= b.id and c.name 	= 'companyType' and c.value = 'PUB')  "
									+ " inner join 	ticketitemproperties 	c1 	on (c1.pos 	= c.pos and c1.localid 	= c.localid and c1.ticketnumber = c.ticketnumber and c1.opendate 	= c.opendate and c1.id 	= b.id and c1.name 	= 'companyNit') "
									+ " inner join 	ticketitemproperties 	c2 	on (c2.pos 	= c.pos and c2.localid 	= c.localid and c2.ticketnumber = c.ticketnumber and c2.opendate 	= c.opendate and c2.id 	= b.id and c2.name 	= 'nFactura') "
									+ " inner join 	ticketitemproperties 	c3 	on (c3.pos 	= c.pos and c3.localid 	= c.localid and c3.ticketnumber = c.ticketnumber and c3.opendate 	= c.opendate and c3.id 	= b.id and c3.name 	= 'vFactura') "
									+ "	inner join 	payments 				d 	on (d.pos 	= b.pos and d.localid 	= b.localid and d.ticketnumber 	= b.ticketnumber and d.opendate 	= b.opendate and d.id = b.id) ";

//				String where = " where a.documenttype = 'service' and a.opendate between ? and ? and c1.value = ?";							

				if(ManejoParam.getParam("PRUEBAS").equals("SI"))
				{
					fechaDesde = fecha + " "+ GeneralConstants.HORA_DESDE_DIA;
					fechaHasta = fecha + " "+ GeneralConstants.HORA_HASTA_DIA;					
					where = " where a.documenttype = 'service' and a.opendate between '"+ fechaDesde +"' and '"+ fechaHasta +"' and c1.value = '"+nit+"' and d.state = 'AUTHORIZED'";
				}
				else
				{
					fechaDesde = Formatos.stringDateToOracleFormat(fecha, "01:01");
					fechaHasta = Formatos.stringDateToOracleFormat(fecha, "23:59");
					where = " where a.documenttype = 'service' and a.opendate between "+ fechaDesde +" and "+ fechaHasta +" and c1.value = '"+nit+"' and d.state = 'AUTHORIZED'";
				}
												
				con = getConnection();
				
				pre = con.prepareStatement(sql + where);
				
//				pre.setString(1, fechaDesde);
//				pre.setString(2, fechaHasta);
//				pre.setString(3, nit);
				
				rs  = pre.executeQuery();
				
				recaudos = new ArrayList<RecaudoServiciosPublicosVO>();
				
				while(rs.next())
				{
					recaudoServiciosPublicosVO = new RecaudoServiciosPublicosVO();
					
					recaudoServiciosPublicosVO.setCodigoSucursal(rs.getString("localid"));
					recaudoServiciosPublicosVO.setFechaRecaudo(rs.getString("payment_fecha"));
					recaudoServiciosPublicosVO.setNit(nit);
					recaudoServiciosPublicosVO.setValor(rs.getBigDecimal("amount").toString());
					recaudoServiciosPublicosVO.setReferencia1(rs.getString("nfactura"));
					recaudoServiciosPublicosVO.setReferencia2(rs.getString("vfactura"));
					
					recaudos.add(recaudoServiciosPublicosVO);
				}
			}
			catch(Exception e)
			{
				Logger.getLogger(RecaudosServiciosPublicosDAOImpl.class.getName()).log(Level.SEVERE, "Problema consultando Nits ", e);
				throw new DAOException("Problema consultando Nits "+e);
			}
			finally
			{
				closeResultSet(rs);
				closePreparedStatement(pre);
				closeConnection(con);
			}
		
		return recaudos;
	}

}
